SET NOCOUNT ON
GO
USE [HotelManagmentSystem]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[sp_Bill_Insert]') and OBJECTPROPERTY(id, N'IsProcedure') = 1) drop procedure [dbo].[sp_Bill_Insert]
GO

CREATE PROCEDURE [dbo].[sp_Bill_Insert]
	@iReservationID int,
	@sComent text,
	@iBillID int OUTPUT
AS
SET NOCOUNT ON
INSERT [dbo].[Bill]
(
	[ReservationID],
	[Coment]
)
VALUES
(
	@iReservationID,
	@sComent
)
SELECT @iBillID=SCOPE_IDENTITY()
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[sp_Bill_Update]') and OBJECTPROPERTY(id, N'IsProcedure') = 1) drop procedure [dbo].[sp_Bill_Update]
GO

CREATE PROCEDURE [dbo].[sp_Bill_Update]
	@iBillID int,
	@iReservationID int,
	@sComent text
AS
SET NOCOUNT ON
UPDATE [dbo].[Bill]
SET 
	[ReservationID] = @iReservationID,
	[Coment] = @sComent
WHERE
	[BillID] = @iBillID
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[sp_Bill_UpdateAllWReservationIDLogic]') and OBJECTPROPERTY(id, N'IsProcedure') = 1) drop procedure [dbo].[sp_Bill_UpdateAllWReservationIDLogic]
GO

CREATE PROCEDURE [dbo].[sp_Bill_UpdateAllWReservationIDLogic]
	@iReservationID int,
	@iReservationIDOld int,
AS
SET NOCOUNT ON
UPDATE [dbo].[Bill]
SET
	[ReservationID] = @iReservationID
WHERE
	[ReservationID] = @iReservationIDOld
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[sp_Bill_Delete]') and OBJECTPROPERTY(id, N'IsProcedure') = 1) drop procedure [dbo].[sp_Bill_Delete]
GO

CREATE PROCEDURE [dbo].[sp_Bill_Delete]
	@iBillID int
AS
SET NOCOUNT ON
DELETE FROM [dbo].[Bill]
WHERE
	[BillID] = @iBillID
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[sp_Bill_SelectOne]') and OBJECTPROPERTY(id, N'IsProcedure') = 1) drop procedure [dbo].[sp_Bill_SelectOne]
GO

CREATE PROCEDURE [dbo].[sp_Bill_SelectOne]
	@iBillID int
AS
SET NOCOUNT ON
SELECT
	[BillID],
	[ReservationID],
	[Coment]
FROM [dbo].[Bill]
WHERE
	[BillID] = @iBillID
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[sp_Bill_SelectAll]') and OBJECTPROPERTY(id, N'IsProcedure') = 1) drop procedure [dbo].[sp_Bill_SelectAll]
GO

CREATE PROCEDURE [dbo].[sp_Bill_SelectAll]
AS
SET NOCOUNT ON
SELECT
	[BillID],
	[ReservationID],
	[Coment]
FROM [dbo].[Bill]
ORDER BY 
	[BillID] ASC
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[sp_Bill_SelectAllWReservationIDLogic]') and OBJECTPROPERTY(id, N'IsProcedure') = 1) drop procedure [dbo].[sp_Bill_SelectAllWReservationIDLogic]
GO

CREATE PROCEDURE [dbo].[sp_Bill_SelectAllWReservationIDLogic]
	@iReservationID int
AS
SET NOCOUNT ON
SELECT
	[BillID],
	[ReservationID],
	[Coment]
FROM [dbo].[Bill]
WHERE
	[ReservationID] = @iReservationID
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[sp_BillItem_Insert]') and OBJECTPROPERTY(id, N'IsProcedure') = 1) drop procedure [dbo].[sp_BillItem_Insert]
GO

CREATE PROCEDURE [dbo].[sp_BillItem_Insert]
	@iRoomID int,
	@sBillItemTypeID nvarchar(10),
	@sBillItemDescription nvarchar(50),
	@iBillItemAmount int,
	@daBillChargeDate datetime,
	@iDiscountID int,
	@iBillID int,
	@iBillItemID int OUTPUT
AS
SET NOCOUNT ON
INSERT [dbo].[BillItem]
(
	[RoomID],
	[BillItemTypeID],
	[BillItemDescription],
	[BillItemAmount],
	[BillChargeDate],
	[DiscountID],
	[BillID]
)
VALUES
(
	@iRoomID,
	@sBillItemTypeID,
	@sBillItemDescription,
	@iBillItemAmount,
	@daBillChargeDate,
	@iDiscountID,
	@iBillID
)
SELECT @iBillItemID=SCOPE_IDENTITY()
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[sp_BillItem_Update]') and OBJECTPROPERTY(id, N'IsProcedure') = 1) drop procedure [dbo].[sp_BillItem_Update]
GO

CREATE PROCEDURE [dbo].[sp_BillItem_Update]
	@iBillItemID int,
	@iRoomID int,
	@sBillItemTypeID nvarchar(10),
	@sBillItemDescription nvarchar(50),
	@iBillItemAmount int,
	@daBillChargeDate datetime,
	@iDiscountID int,
	@iBillID int
AS
SET NOCOUNT ON
UPDATE [dbo].[BillItem]
SET 
	[RoomID] = @iRoomID,
	[BillItemTypeID] = @sBillItemTypeID,
	[BillItemDescription] = @sBillItemDescription,
	[BillItemAmount] = @iBillItemAmount,
	[BillChargeDate] = @daBillChargeDate,
	[DiscountID] = @iDiscountID,
	[BillID] = @iBillID
WHERE
	[BillItemID] = @iBillItemID
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[sp_BillItem_UpdateAllWRoomIDLogic]') and OBJECTPROPERTY(id, N'IsProcedure') = 1) drop procedure [dbo].[sp_BillItem_UpdateAllWRoomIDLogic]
GO

CREATE PROCEDURE [dbo].[sp_BillItem_UpdateAllWRoomIDLogic]
	@iRoomID int,
	@iRoomIDOld int,
AS
SET NOCOUNT ON
UPDATE [dbo].[BillItem]
SET
	[RoomID] = @iRoomID
WHERE
	[RoomID] = @iRoomIDOld
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[sp_BillItem_UpdateAllWBillItemTypeIDLogic]') and OBJECTPROPERTY(id, N'IsProcedure') = 1) drop procedure [dbo].[sp_BillItem_UpdateAllWBillItemTypeIDLogic]
GO

CREATE PROCEDURE [dbo].[sp_BillItem_UpdateAllWBillItemTypeIDLogic]
	@sBillItemTypeID nvarchar(10),
	@sBillItemTypeIDOld nvarchar(10),
AS
SET NOCOUNT ON
UPDATE [dbo].[BillItem]
SET
	[BillItemTypeID] = @sBillItemTypeID
WHERE
	[BillItemTypeID] = @sBillItemTypeIDOld
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[sp_BillItem_UpdateAllWDiscountIDLogic]') and OBJECTPROPERTY(id, N'IsProcedure') = 1) drop procedure [dbo].[sp_BillItem_UpdateAllWDiscountIDLogic]
GO

CREATE PROCEDURE [dbo].[sp_BillItem_UpdateAllWDiscountIDLogic]
	@iDiscountID int,
	@iDiscountIDOld int,
AS
SET NOCOUNT ON
UPDATE [dbo].[BillItem]
SET
	[DiscountID] = @iDiscountID
WHERE
	[DiscountID] = @iDiscountIDOld
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[sp_BillItem_UpdateAllWBillIDLogic]') and OBJECTPROPERTY(id, N'IsProcedure') = 1) drop procedure [dbo].[sp_BillItem_UpdateAllWBillIDLogic]
GO

CREATE PROCEDURE [dbo].[sp_BillItem_UpdateAllWBillIDLogic]
	@iBillID int,
	@iBillIDOld int,
AS
SET NOCOUNT ON
UPDATE [dbo].[BillItem]
SET
	[BillID] = @iBillID
WHERE
	[BillID] = @iBillIDOld
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[sp_BillItem_Delete]') and OBJECTPROPERTY(id, N'IsProcedure') = 1) drop procedure [dbo].[sp_BillItem_Delete]
GO

CREATE PROCEDURE [dbo].[sp_BillItem_Delete]
	@iBillItemID int
AS
SET NOCOUNT ON
DELETE FROM [dbo].[BillItem]
WHERE
	[BillItemID] = @iBillItemID
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[sp_BillItem_SelectOne]') and OBJECTPROPERTY(id, N'IsProcedure') = 1) drop procedure [dbo].[sp_BillItem_SelectOne]
GO

CREATE PROCEDURE [dbo].[sp_BillItem_SelectOne]
	@iBillItemID int
AS
SET NOCOUNT ON
SELECT
	[BillItemID],
	[RoomID],
	[BillItemTypeID],
	[BillItemDescription],
	[BillItemAmount],
	[BillChargeDate],
	[DiscountID],
	[BillID]
FROM [dbo].[BillItem]
WHERE
	[BillItemID] = @iBillItemID
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[sp_BillItem_SelectAll]') and OBJECTPROPERTY(id, N'IsProcedure') = 1) drop procedure [dbo].[sp_BillItem_SelectAll]
GO

CREATE PROCEDURE [dbo].[sp_BillItem_SelectAll]
AS
SET NOCOUNT ON
SELECT
	[BillItemID],
	[RoomID],
	[BillItemTypeID],
	[BillItemDescription],
	[BillItemAmount],
	[BillChargeDate],
	[DiscountID],
	[BillID]
FROM [dbo].[BillItem]
ORDER BY 
	[BillItemID] ASC
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[sp_BillItem_SelectAllWRoomIDLogic]') and OBJECTPROPERTY(id, N'IsProcedure') = 1) drop procedure [dbo].[sp_BillItem_SelectAllWRoomIDLogic]
GO

CREATE PROCEDURE [dbo].[sp_BillItem_SelectAllWRoomIDLogic]
	@iRoomID int
AS
SET NOCOUNT ON
SELECT
	[BillItemID],
	[RoomID],
	[BillItemTypeID],
	[BillItemDescription],
	[BillItemAmount],
	[BillChargeDate],
	[DiscountID],
	[BillID]
FROM [dbo].[BillItem]
WHERE
	[RoomID] = @iRoomID
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[sp_BillItem_SelectAllWBillItemTypeIDLogic]') and OBJECTPROPERTY(id, N'IsProcedure') = 1) drop procedure [dbo].[sp_BillItem_SelectAllWBillItemTypeIDLogic]
GO

CREATE PROCEDURE [dbo].[sp_BillItem_SelectAllWBillItemTypeIDLogic]
	@sBillItemTypeID nvarchar(10)
AS
SET NOCOUNT ON
SELECT
	[BillItemID],
	[RoomID],
	[BillItemTypeID],
	[BillItemDescription],
	[BillItemAmount],
	[BillChargeDate],
	[DiscountID],
	[BillID]
FROM [dbo].[BillItem]
WHERE
	[BillItemTypeID] = @sBillItemTypeID
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[sp_BillItem_SelectAllWDiscountIDLogic]') and OBJECTPROPERTY(id, N'IsProcedure') = 1) drop procedure [dbo].[sp_BillItem_SelectAllWDiscountIDLogic]
GO

CREATE PROCEDURE [dbo].[sp_BillItem_SelectAllWDiscountIDLogic]
	@iDiscountID int
AS
SET NOCOUNT ON
SELECT
	[BillItemID],
	[RoomID],
	[BillItemTypeID],
	[BillItemDescription],
	[BillItemAmount],
	[BillChargeDate],
	[DiscountID],
	[BillID]
FROM [dbo].[BillItem]
WHERE
	[DiscountID] = @iDiscountID
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[sp_BillItem_SelectAllWBillIDLogic]') and OBJECTPROPERTY(id, N'IsProcedure') = 1) drop procedure [dbo].[sp_BillItem_SelectAllWBillIDLogic]
GO

CREATE PROCEDURE [dbo].[sp_BillItem_SelectAllWBillIDLogic]
	@iBillID int
AS
SET NOCOUNT ON
SELECT
	[BillItemID],
	[RoomID],
	[BillItemTypeID],
	[BillItemDescription],
	[BillItemAmount],
	[BillChargeDate],
	[DiscountID],
	[BillID]
FROM [dbo].[BillItem]
WHERE
	[BillID] = @iBillID
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[sp_BillItemType_Insert]') and OBJECTPROPERTY(id, N'IsProcedure') = 1) drop procedure [dbo].[sp_BillItemType_Insert]
GO

CREATE PROCEDURE [dbo].[sp_BillItemType_Insert]
	@sBillItemTypeID nvarchar(10),
	@sBillItemType nvarchar(30),
	@sDescription nvarchar(50)
AS
SET NOCOUNT ON
INSERT [dbo].[BillItemType]
(
	[BillItemTypeID],
	[BillItemType],
	[Description]
)
VALUES
(
	@sBillItemTypeID,
	@sBillItemType,
	@sDescription
)
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[sp_BillItemType_Update]') and OBJECTPROPERTY(id, N'IsProcedure') = 1) drop procedure [dbo].[sp_BillItemType_Update]
GO

CREATE PROCEDURE [dbo].[sp_BillItemType_Update]
	@sBillItemTypeID nvarchar(10),
	@sBillItemType nvarchar(30),
	@sDescription nvarchar(50)
AS
SET NOCOUNT ON
UPDATE [dbo].[BillItemType]
SET 
	[BillItemType] = @sBillItemType,
	[Description] = @sDescription
WHERE
	[BillItemTypeID] = @sBillItemTypeID
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[sp_BillItemType_Delete]') and OBJECTPROPERTY(id, N'IsProcedure') = 1) drop procedure [dbo].[sp_BillItemType_Delete]
GO

CREATE PROCEDURE [dbo].[sp_BillItemType_Delete]
	@sBillItemTypeID nvarchar(10)
AS
SET NOCOUNT ON
DELETE FROM [dbo].[BillItemType]
WHERE
	[BillItemTypeID] = @sBillItemTypeID
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[sp_BillItemType_SelectOne]') and OBJECTPROPERTY(id, N'IsProcedure') = 1) drop procedure [dbo].[sp_BillItemType_SelectOne]
GO

CREATE PROCEDURE [dbo].[sp_BillItemType_SelectOne]
	@sBillItemTypeID nvarchar(10)
AS
SET NOCOUNT ON
SELECT
	[BillItemTypeID],
	[BillItemType],
	[Description]
FROM [dbo].[BillItemType]
WHERE
	[BillItemTypeID] = @sBillItemTypeID
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[sp_BillItemType_SelectAll]') and OBJECTPROPERTY(id, N'IsProcedure') = 1) drop procedure [dbo].[sp_BillItemType_SelectAll]
GO

CREATE PROCEDURE [dbo].[sp_BillItemType_SelectAll]
AS
SET NOCOUNT ON
SELECT
	[BillItemTypeID],
	[BillItemType],
	[Description]
FROM [dbo].[BillItemType]
ORDER BY 
	[BillItemTypeID] ASC
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[sp_CheckIn_Insert]') and OBJECTPROPERTY(id, N'IsProcedure') = 1) drop procedure [dbo].[sp_CheckIn_Insert]
GO

CREATE PROCEDURE [dbo].[sp_CheckIn_Insert]
	@iReservationID int,
	@iRoomID int,
	@daCheckinDate datetime,
	@iCheckinID int OUTPUT
AS
SET NOCOUNT ON
INSERT [dbo].[CheckIn]
(
	[ReservationID],
	[RoomID],
	[CheckinDate]
)
VALUES
(
	@iReservationID,
	@iRoomID,
	@daCheckinDate
)
SELECT @iCheckinID=SCOPE_IDENTITY()
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[sp_CheckIn_Update]') and OBJECTPROPERTY(id, N'IsProcedure') = 1) drop procedure [dbo].[sp_CheckIn_Update]
GO

CREATE PROCEDURE [dbo].[sp_CheckIn_Update]
	@iCheckinID int,
	@iReservationID int,
	@iRoomID int,
	@daCheckinDate datetime
AS
SET NOCOUNT ON
UPDATE [dbo].[CheckIn]
SET 
	[ReservationID] = @iReservationID,
	[RoomID] = @iRoomID,
	[CheckinDate] = @daCheckinDate
WHERE
	[CheckinID] = @iCheckinID
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[sp_CheckIn_UpdateAllWReservationIDLogic]') and OBJECTPROPERTY(id, N'IsProcedure') = 1) drop procedure [dbo].[sp_CheckIn_UpdateAllWReservationIDLogic]
GO

CREATE PROCEDURE [dbo].[sp_CheckIn_UpdateAllWReservationIDLogic]
	@iReservationID int,
	@iReservationIDOld int,
AS
SET NOCOUNT ON
UPDATE [dbo].[CheckIn]
SET
	[ReservationID] = @iReservationID
WHERE
	[ReservationID] = @iReservationIDOld
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[sp_CheckIn_UpdateAllWRoomIDLogic]') and OBJECTPROPERTY(id, N'IsProcedure') = 1) drop procedure [dbo].[sp_CheckIn_UpdateAllWRoomIDLogic]
GO

CREATE PROCEDURE [dbo].[sp_CheckIn_UpdateAllWRoomIDLogic]
	@iRoomID int,
	@iRoomIDOld int,
AS
SET NOCOUNT ON
UPDATE [dbo].[CheckIn]
SET
	[RoomID] = @iRoomID
WHERE
	[RoomID] = @iRoomIDOld
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[sp_CheckIn_Delete]') and OBJECTPROPERTY(id, N'IsProcedure') = 1) drop procedure [dbo].[sp_CheckIn_Delete]
GO

CREATE PROCEDURE [dbo].[sp_CheckIn_Delete]
	@iCheckinID int
AS
SET NOCOUNT ON
DELETE FROM [dbo].[CheckIn]
WHERE
	[CheckinID] = @iCheckinID
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[sp_CheckIn_SelectOne]') and OBJECTPROPERTY(id, N'IsProcedure') = 1) drop procedure [dbo].[sp_CheckIn_SelectOne]
GO

CREATE PROCEDURE [dbo].[sp_CheckIn_SelectOne]
	@iCheckinID int
AS
SET NOCOUNT ON
SELECT
	[CheckinID],
	[ReservationID],
	[RoomID],
	[CheckinDate]
FROM [dbo].[CheckIn]
WHERE
	[CheckinID] = @iCheckinID
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[sp_CheckIn_SelectAll]') and OBJECTPROPERTY(id, N'IsProcedure') = 1) drop procedure [dbo].[sp_CheckIn_SelectAll]
GO

CREATE PROCEDURE [dbo].[sp_CheckIn_SelectAll]
AS
SET NOCOUNT ON
SELECT
	[CheckinID],
	[ReservationID],
	[RoomID],
	[CheckinDate]
FROM [dbo].[CheckIn]
ORDER BY 
	[CheckinID] ASC
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[sp_CheckIn_SelectAllWReservationIDLogic]') and OBJECTPROPERTY(id, N'IsProcedure') = 1) drop procedure [dbo].[sp_CheckIn_SelectAllWReservationIDLogic]
GO

CREATE PROCEDURE [dbo].[sp_CheckIn_SelectAllWReservationIDLogic]
	@iReservationID int
AS
SET NOCOUNT ON
SELECT
	[CheckinID],
	[ReservationID],
	[RoomID],
	[CheckinDate]
FROM [dbo].[CheckIn]
WHERE
	[ReservationID] = @iReservationID
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[sp_CheckIn_SelectAllWRoomIDLogic]') and OBJECTPROPERTY(id, N'IsProcedure') = 1) drop procedure [dbo].[sp_CheckIn_SelectAllWRoomIDLogic]
GO

CREATE PROCEDURE [dbo].[sp_CheckIn_SelectAllWRoomIDLogic]
	@iRoomID int
AS
SET NOCOUNT ON
SELECT
	[CheckinID],
	[ReservationID],
	[RoomID],
	[CheckinDate]
FROM [dbo].[CheckIn]
WHERE
	[RoomID] = @iRoomID
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[sp_CheckOut_Insert]') and OBJECTPROPERTY(id, N'IsProcedure') = 1) drop procedure [dbo].[sp_CheckOut_Insert]
GO

CREATE PROCEDURE [dbo].[sp_CheckOut_Insert]
	@iReservationID int,
	@daCheckoutDate datetime,
	@iCheckoutID int OUTPUT
AS
SET NOCOUNT ON
INSERT [dbo].[CheckOut]
(
	[ReservationID],
	[CheckoutDate]
)
VALUES
(
	@iReservationID,
	@daCheckoutDate
)
SELECT @iCheckoutID=SCOPE_IDENTITY()
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[sp_CheckOut_Update]') and OBJECTPROPERTY(id, N'IsProcedure') = 1) drop procedure [dbo].[sp_CheckOut_Update]
GO

CREATE PROCEDURE [dbo].[sp_CheckOut_Update]
	@iCheckoutID int,
	@iReservationID int,
	@daCheckoutDate datetime
AS
SET NOCOUNT ON
UPDATE [dbo].[CheckOut]
SET 
	[ReservationID] = @iReservationID,
	[CheckoutDate] = @daCheckoutDate
WHERE
	[CheckoutID] = @iCheckoutID
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[sp_CheckOut_UpdateAllWReservationIDLogic]') and OBJECTPROPERTY(id, N'IsProcedure') = 1) drop procedure [dbo].[sp_CheckOut_UpdateAllWReservationIDLogic]
GO

CREATE PROCEDURE [dbo].[sp_CheckOut_UpdateAllWReservationIDLogic]
	@iReservationID int,
	@iReservationIDOld int,
AS
SET NOCOUNT ON
UPDATE [dbo].[CheckOut]
SET
	[ReservationID] = @iReservationID
WHERE
	[ReservationID] = @iReservationIDOld
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[sp_CheckOut_Delete]') and OBJECTPROPERTY(id, N'IsProcedure') = 1) drop procedure [dbo].[sp_CheckOut_Delete]
GO

CREATE PROCEDURE [dbo].[sp_CheckOut_Delete]
	@iCheckoutID int
AS
SET NOCOUNT ON
DELETE FROM [dbo].[CheckOut]
WHERE
	[CheckoutID] = @iCheckoutID
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[sp_CheckOut_SelectOne]') and OBJECTPROPERTY(id, N'IsProcedure') = 1) drop procedure [dbo].[sp_CheckOut_SelectOne]
GO

CREATE PROCEDURE [dbo].[sp_CheckOut_SelectOne]
	@iCheckoutID int
AS
SET NOCOUNT ON
SELECT
	[CheckoutID],
	[ReservationID],
	[CheckoutDate]
FROM [dbo].[CheckOut]
WHERE
	[CheckoutID] = @iCheckoutID
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[sp_CheckOut_SelectAll]') and OBJECTPROPERTY(id, N'IsProcedure') = 1) drop procedure [dbo].[sp_CheckOut_SelectAll]
GO

CREATE PROCEDURE [dbo].[sp_CheckOut_SelectAll]
AS
SET NOCOUNT ON
SELECT
	[CheckoutID],
	[ReservationID],
	[CheckoutDate]
FROM [dbo].[CheckOut]
ORDER BY 
	[CheckoutID] ASC
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[sp_CheckOut_SelectAllWReservationIDLogic]') and OBJECTPROPERTY(id, N'IsProcedure') = 1) drop procedure [dbo].[sp_CheckOut_SelectAllWReservationIDLogic]
GO

CREATE PROCEDURE [dbo].[sp_CheckOut_SelectAllWReservationIDLogic]
	@iReservationID int
AS
SET NOCOUNT ON
SELECT
	[CheckoutID],
	[ReservationID],
	[CheckoutDate]
FROM [dbo].[CheckOut]
WHERE
	[ReservationID] = @iReservationID
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[sp_Customer_Insert]') and OBJECTPROPERTY(id, N'IsProcedure') = 1) drop procedure [dbo].[sp_Customer_Insert]
GO

CREATE PROCEDURE [dbo].[sp_Customer_Insert]
	@sFirstName nvarchar(10),
	@sMiddleName nvarchar(10),
	@sLastName nvarchar(10),
	@sAddStreet nvarchar(30),
	@sAddCity nvarchar(30),
	@sAddSate nvarchar(50),
	@sAddZip nvarchar(10),
	@sAddCountry nvarchar(30),
	@iHomePhone int,
	@iWorkPhone int,
	@sEmail nvarchar(100),
	@iLastVisited int,
	@iCCNumber int,
	@daCCExpiry datetime,
	@iCustomerID int OUTPUT
AS
SET NOCOUNT ON
INSERT [dbo].[Customer]
(
	[FirstName],
	[MiddleName],
	[LastName],
	[AddStreet],
	[AddCity],
	[AddSate],
	[AddZip],
	[AddCountry],
	[HomePhone],
	[WorkPhone],
	[Email],
	[LastVisited],
	[CCNumber],
	[CCExpiry]
)
VALUES
(
	@sFirstName,
	@sMiddleName,
	@sLastName,
	@sAddStreet,
	@sAddCity,
	@sAddSate,
	@sAddZip,
	@sAddCountry,
	@iHomePhone,
	@iWorkPhone,
	@sEmail,
	@iLastVisited,
	@iCCNumber,
	@daCCExpiry
)
SELECT @iCustomerID=SCOPE_IDENTITY()
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[sp_Customer_Update]') and OBJECTPROPERTY(id, N'IsProcedure') = 1) drop procedure [dbo].[sp_Customer_Update]
GO

CREATE PROCEDURE [dbo].[sp_Customer_Update]
	@iCustomerID int,
	@sFirstName nvarchar(10),
	@sMiddleName nvarchar(10),
	@sLastName nvarchar(10),
	@sAddStreet nvarchar(30),
	@sAddCity nvarchar(30),
	@sAddSate nvarchar(50),
	@sAddZip nvarchar(10),
	@sAddCountry nvarchar(30),
	@iHomePhone int,
	@iWorkPhone int,
	@sEmail nvarchar(100),
	@iLastVisited int,
	@iCCNumber int,
	@daCCExpiry datetime
AS
SET NOCOUNT ON
UPDATE [dbo].[Customer]
SET 
	[FirstName] = @sFirstName,
	[MiddleName] = @sMiddleName,
	[LastName] = @sLastName,
	[AddStreet] = @sAddStreet,
	[AddCity] = @sAddCity,
	[AddSate] = @sAddSate,
	[AddZip] = @sAddZip,
	[AddCountry] = @sAddCountry,
	[HomePhone] = @iHomePhone,
	[WorkPhone] = @iWorkPhone,
	[Email] = @sEmail,
	[LastVisited] = @iLastVisited,
	[CCNumber] = @iCCNumber,
	[CCExpiry] = @daCCExpiry
WHERE
	[CustomerID] = @iCustomerID
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[sp_Customer_Delete]') and OBJECTPROPERTY(id, N'IsProcedure') = 1) drop procedure [dbo].[sp_Customer_Delete]
GO

CREATE PROCEDURE [dbo].[sp_Customer_Delete]
	@iCustomerID int
AS
SET NOCOUNT ON
DELETE FROM [dbo].[Customer]
WHERE
	[CustomerID] = @iCustomerID
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[sp_Customer_SelectOne]') and OBJECTPROPERTY(id, N'IsProcedure') = 1) drop procedure [dbo].[sp_Customer_SelectOne]
GO

CREATE PROCEDURE [dbo].[sp_Customer_SelectOne]
	@iCustomerID int
AS
SET NOCOUNT ON
SELECT
	[CustomerID],
	[FirstName],
	[MiddleName],
	[LastName],
	[AddStreet],
	[AddCity],
	[AddSate],
	[AddZip],
	[AddCountry],
	[HomePhone],
	[WorkPhone],
	[Email],
	[LastVisited],
	[CCNumber],
	[CCExpiry]
FROM [dbo].[Customer]
WHERE
	[CustomerID] = @iCustomerID
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[sp_Customer_SelectAll]') and OBJECTPROPERTY(id, N'IsProcedure') = 1) drop procedure [dbo].[sp_Customer_SelectAll]
GO

CREATE PROCEDURE [dbo].[sp_Customer_SelectAll]
AS
SET NOCOUNT ON
SELECT
	[CustomerID],
	[FirstName],
	[MiddleName],
	[LastName],
	[AddStreet],
	[AddCity],
	[AddSate],
	[AddZip],
	[AddCountry],
	[HomePhone],
	[WorkPhone],
	[Email],
	[LastVisited],
	[CCNumber],
	[CCExpiry]
FROM [dbo].[Customer]
ORDER BY 
	[CustomerID] ASC
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[sp_Discount_Insert]') and OBJECTPROPERTY(id, N'IsProcedure') = 1) drop procedure [dbo].[sp_Discount_Insert]
GO

CREATE PROCEDURE [dbo].[sp_Discount_Insert]
	@sDiscountName nvarchar(50),
	@sDiscountPercent nvarchar(10),
	@iDiscountAmount int,
	@iDiscountID int OUTPUT
AS
SET NOCOUNT ON
INSERT [dbo].[Discount]
(
	[DiscountName],
	[DiscountPercent],
	[DiscountAmount]
)
VALUES
(
	@sDiscountName,
	@sDiscountPercent,
	@iDiscountAmount
)
SELECT @iDiscountID=SCOPE_IDENTITY()
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[sp_Discount_Update]') and OBJECTPROPERTY(id, N'IsProcedure') = 1) drop procedure [dbo].[sp_Discount_Update]
GO

CREATE PROCEDURE [dbo].[sp_Discount_Update]
	@iDiscountID int,
	@sDiscountName nvarchar(50),
	@sDiscountPercent nvarchar(10),
	@iDiscountAmount int
AS
SET NOCOUNT ON
UPDATE [dbo].[Discount]
SET 
	[DiscountName] = @sDiscountName,
	[DiscountPercent] = @sDiscountPercent,
	[DiscountAmount] = @iDiscountAmount
WHERE
	[DiscountID] = @iDiscountID
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[sp_Discount_Delete]') and OBJECTPROPERTY(id, N'IsProcedure') = 1) drop procedure [dbo].[sp_Discount_Delete]
GO

CREATE PROCEDURE [dbo].[sp_Discount_Delete]
	@iDiscountID int
AS
SET NOCOUNT ON
DELETE FROM [dbo].[Discount]
WHERE
	[DiscountID] = @iDiscountID
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[sp_Discount_SelectOne]') and OBJECTPROPERTY(id, N'IsProcedure') = 1) drop procedure [dbo].[sp_Discount_SelectOne]
GO

CREATE PROCEDURE [dbo].[sp_Discount_SelectOne]
	@iDiscountID int
AS
SET NOCOUNT ON
SELECT
	[DiscountID],
	[DiscountName],
	[DiscountPercent],
	[DiscountAmount]
FROM [dbo].[Discount]
WHERE
	[DiscountID] = @iDiscountID
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[sp_Discount_SelectAll]') and OBJECTPROPERTY(id, N'IsProcedure') = 1) drop procedure [dbo].[sp_Discount_SelectAll]
GO

CREATE PROCEDURE [dbo].[sp_Discount_SelectAll]
AS
SET NOCOUNT ON
SELECT
	[DiscountID],
	[DiscountName],
	[DiscountPercent],
	[DiscountAmount]
FROM [dbo].[Discount]
ORDER BY 
	[DiscountID] ASC
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[sp_Login_Insert]') and OBJECTPROPERTY(id, N'IsProcedure') = 1) drop procedure [dbo].[sp_Login_Insert]
GO

CREATE PROCEDURE [dbo].[sp_Login_Insert]
	@sPassword nvarchar(10),
	@iCustomerID int,
	@iRoleID int,
	@iUserName int OUTPUT
AS
SET NOCOUNT ON
INSERT [dbo].[Login]
(
	[Password],
	[CustomerID],
	[RoleID]
)
VALUES
(
	@sPassword,
	@iCustomerID,
	@iRoleID
)
SELECT @iUserName=SCOPE_IDENTITY()
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[sp_Login_Update]') and OBJECTPROPERTY(id, N'IsProcedure') = 1) drop procedure [dbo].[sp_Login_Update]
GO

CREATE PROCEDURE [dbo].[sp_Login_Update]
	@iUserName int,
	@sPassword nvarchar(10),
	@iCustomerID int,
	@iRoleID int
AS
SET NOCOUNT ON
UPDATE [dbo].[Login]
SET 
	[Password] = @sPassword,
	[CustomerID] = @iCustomerID,
	[RoleID] = @iRoleID
WHERE
	[UserName] = @iUserName
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[sp_Login_UpdateAllWCustomerIDLogic]') and OBJECTPROPERTY(id, N'IsProcedure') = 1) drop procedure [dbo].[sp_Login_UpdateAllWCustomerIDLogic]
GO

CREATE PROCEDURE [dbo].[sp_Login_UpdateAllWCustomerIDLogic]
	@iCustomerID int,
	@iCustomerIDOld int,
AS
SET NOCOUNT ON
UPDATE [dbo].[Login]
SET
	[CustomerID] = @iCustomerID
WHERE
	[CustomerID] = @iCustomerIDOld
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[sp_Login_UpdateAllWRoleIDLogic]') and OBJECTPROPERTY(id, N'IsProcedure') = 1) drop procedure [dbo].[sp_Login_UpdateAllWRoleIDLogic]
GO

CREATE PROCEDURE [dbo].[sp_Login_UpdateAllWRoleIDLogic]
	@iRoleID int,
	@iRoleIDOld int,
AS
SET NOCOUNT ON
UPDATE [dbo].[Login]
SET
	[RoleID] = @iRoleID
WHERE
	[RoleID] = @iRoleIDOld
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[sp_Login_Delete]') and OBJECTPROPERTY(id, N'IsProcedure') = 1) drop procedure [dbo].[sp_Login_Delete]
GO

CREATE PROCEDURE [dbo].[sp_Login_Delete]
	@iUserName int
AS
SET NOCOUNT ON
DELETE FROM [dbo].[Login]
WHERE
	[UserName] = @iUserName
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[sp_Login_SelectOne]') and OBJECTPROPERTY(id, N'IsProcedure') = 1) drop procedure [dbo].[sp_Login_SelectOne]
GO

CREATE PROCEDURE [dbo].[sp_Login_SelectOne]
	@iUserName int
AS
SET NOCOUNT ON
SELECT
	[UserName],
	[Password],
	[CustomerID],
	[RoleID]
FROM [dbo].[Login]
WHERE
	[UserName] = @iUserName
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[sp_Login_SelectAll]') and OBJECTPROPERTY(id, N'IsProcedure') = 1) drop procedure [dbo].[sp_Login_SelectAll]
GO

CREATE PROCEDURE [dbo].[sp_Login_SelectAll]
AS
SET NOCOUNT ON
SELECT
	[UserName],
	[Password],
	[CustomerID],
	[RoleID]
FROM [dbo].[Login]
ORDER BY 
	[UserName] ASC
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[sp_Login_SelectAllWCustomerIDLogic]') and OBJECTPROPERTY(id, N'IsProcedure') = 1) drop procedure [dbo].[sp_Login_SelectAllWCustomerIDLogic]
GO

CREATE PROCEDURE [dbo].[sp_Login_SelectAllWCustomerIDLogic]
	@iCustomerID int
AS
SET NOCOUNT ON
SELECT
	[UserName],
	[Password],
	[CustomerID],
	[RoleID]
FROM [dbo].[Login]
WHERE
	[CustomerID] = @iCustomerID
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[sp_Login_SelectAllWRoleIDLogic]') and OBJECTPROPERTY(id, N'IsProcedure') = 1) drop procedure [dbo].[sp_Login_SelectAllWRoleIDLogic]
GO

CREATE PROCEDURE [dbo].[sp_Login_SelectAllWRoleIDLogic]
	@iRoleID int
AS
SET NOCOUNT ON
SELECT
	[UserName],
	[Password],
	[CustomerID],
	[RoleID]
FROM [dbo].[Login]
WHERE
	[RoleID] = @iRoleID
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[sp_Payment_Insert]') and OBJECTPROPERTY(id, N'IsProcedure') = 1) drop procedure [dbo].[sp_Payment_Insert]
GO

CREATE PROCEDURE [dbo].[sp_Payment_Insert]
	@sPaymentTypeID nvarchar(10),
	@iCCNumber int,
	@iCCExpirationMonth int,
	@iCCExpirationYear int,
	@daBillChargeDate datetime,
	@sCCOwner nvarchar(30),
	@iPaymentAmount int,
	@daPaymentDate datetime,
	@iReservationID int,
	@iPaymentID int OUTPUT
AS
SET NOCOUNT ON
INSERT [dbo].[Payment]
(
	[PaymentTypeID],
	[CCNumber],
	[CCExpirationMonth],
	[CCExpirationYear],
	[BillChargeDate],
	[CCOwner],
	[PaymentAmount],
	[PaymentDate],
	[ReservationID]
)
VALUES
(
	@sPaymentTypeID,
	@iCCNumber,
	@iCCExpirationMonth,
	@iCCExpirationYear,
	@daBillChargeDate,
	@sCCOwner,
	@iPaymentAmount,
	@daPaymentDate,
	@iReservationID
)
SELECT @iPaymentID=SCOPE_IDENTITY()
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[sp_Payment_Update]') and OBJECTPROPERTY(id, N'IsProcedure') = 1) drop procedure [dbo].[sp_Payment_Update]
GO

CREATE PROCEDURE [dbo].[sp_Payment_Update]
	@iPaymentID int,
	@sPaymentTypeID nvarchar(10),
	@iCCNumber int,
	@iCCExpirationMonth int,
	@iCCExpirationYear int,
	@daBillChargeDate datetime,
	@sCCOwner nvarchar(30),
	@iPaymentAmount int,
	@daPaymentDate datetime,
	@iReservationID int
AS
SET NOCOUNT ON
UPDATE [dbo].[Payment]
SET 
	[PaymentTypeID] = @sPaymentTypeID,
	[CCNumber] = @iCCNumber,
	[CCExpirationMonth] = @iCCExpirationMonth,
	[CCExpirationYear] = @iCCExpirationYear,
	[BillChargeDate] = @daBillChargeDate,
	[CCOwner] = @sCCOwner,
	[PaymentAmount] = @iPaymentAmount,
	[PaymentDate] = @daPaymentDate,
	[ReservationID] = @iReservationID
WHERE
	[PaymentID] = @iPaymentID
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[sp_Payment_UpdateAllWPaymentTypeIDLogic]') and OBJECTPROPERTY(id, N'IsProcedure') = 1) drop procedure [dbo].[sp_Payment_UpdateAllWPaymentTypeIDLogic]
GO

CREATE PROCEDURE [dbo].[sp_Payment_UpdateAllWPaymentTypeIDLogic]
	@sPaymentTypeID nvarchar(10),
	@sPaymentTypeIDOld nvarchar(10),
AS
SET NOCOUNT ON
UPDATE [dbo].[Payment]
SET
	[PaymentTypeID] = @sPaymentTypeID
WHERE
	[PaymentTypeID] = @sPaymentTypeIDOld
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[sp_Payment_UpdateAllWReservationIDLogic]') and OBJECTPROPERTY(id, N'IsProcedure') = 1) drop procedure [dbo].[sp_Payment_UpdateAllWReservationIDLogic]
GO

CREATE PROCEDURE [dbo].[sp_Payment_UpdateAllWReservationIDLogic]
	@iReservationID int,
	@iReservationIDOld int,
AS
SET NOCOUNT ON
UPDATE [dbo].[Payment]
SET
	[ReservationID] = @iReservationID
WHERE
	[ReservationID] = @iReservationIDOld
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[sp_Payment_Delete]') and OBJECTPROPERTY(id, N'IsProcedure') = 1) drop procedure [dbo].[sp_Payment_Delete]
GO

CREATE PROCEDURE [dbo].[sp_Payment_Delete]
	@iPaymentID int
AS
SET NOCOUNT ON
DELETE FROM [dbo].[Payment]
WHERE
	[PaymentID] = @iPaymentID
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[sp_Payment_SelectOne]') and OBJECTPROPERTY(id, N'IsProcedure') = 1) drop procedure [dbo].[sp_Payment_SelectOne]
GO

CREATE PROCEDURE [dbo].[sp_Payment_SelectOne]
	@iPaymentID int
AS
SET NOCOUNT ON
SELECT
	[PaymentID],
	[PaymentTypeID],
	[CCNumber],
	[CCExpirationMonth],
	[CCExpirationYear],
	[BillChargeDate],
	[CCOwner],
	[PaymentAmount],
	[PaymentDate],
	[ReservationID]
FROM [dbo].[Payment]
WHERE
	[PaymentID] = @iPaymentID
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[sp_Payment_SelectAll]') and OBJECTPROPERTY(id, N'IsProcedure') = 1) drop procedure [dbo].[sp_Payment_SelectAll]
GO

CREATE PROCEDURE [dbo].[sp_Payment_SelectAll]
AS
SET NOCOUNT ON
SELECT
	[PaymentID],
	[PaymentTypeID],
	[CCNumber],
	[CCExpirationMonth],
	[CCExpirationYear],
	[BillChargeDate],
	[CCOwner],
	[PaymentAmount],
	[PaymentDate],
	[ReservationID]
FROM [dbo].[Payment]
ORDER BY 
	[PaymentID] ASC
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[sp_Payment_SelectAllWPaymentTypeIDLogic]') and OBJECTPROPERTY(id, N'IsProcedure') = 1) drop procedure [dbo].[sp_Payment_SelectAllWPaymentTypeIDLogic]
GO

CREATE PROCEDURE [dbo].[sp_Payment_SelectAllWPaymentTypeIDLogic]
	@sPaymentTypeID nvarchar(10)
AS
SET NOCOUNT ON
SELECT
	[PaymentID],
	[PaymentTypeID],
	[CCNumber],
	[CCExpirationMonth],
	[CCExpirationYear],
	[BillChargeDate],
	[CCOwner],
	[PaymentAmount],
	[PaymentDate],
	[ReservationID]
FROM [dbo].[Payment]
WHERE
	[PaymentTypeID] = @sPaymentTypeID
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[sp_Payment_SelectAllWReservationIDLogic]') and OBJECTPROPERTY(id, N'IsProcedure') = 1) drop procedure [dbo].[sp_Payment_SelectAllWReservationIDLogic]
GO

CREATE PROCEDURE [dbo].[sp_Payment_SelectAllWReservationIDLogic]
	@iReservationID int
AS
SET NOCOUNT ON
SELECT
	[PaymentID],
	[PaymentTypeID],
	[CCNumber],
	[CCExpirationMonth],
	[CCExpirationYear],
	[BillChargeDate],
	[CCOwner],
	[PaymentAmount],
	[PaymentDate],
	[ReservationID]
FROM [dbo].[Payment]
WHERE
	[ReservationID] = @iReservationID
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[sp_PaymentType_Insert]') and OBJECTPROPERTY(id, N'IsProcedure') = 1) drop procedure [dbo].[sp_PaymentType_Insert]
GO

CREATE PROCEDURE [dbo].[sp_PaymentType_Insert]
	@sPaymentTypeID nvarchar(10),
	@sPaymentType nvarchar(30),
	@sPaymentTypeDesc nvarchar(30)
AS
SET NOCOUNT ON
INSERT [dbo].[PaymentType]
(
	[PaymentTypeID],
	[PaymentType],
	[PaymentTypeDesc]
)
VALUES
(
	@sPaymentTypeID,
	@sPaymentType,
	@sPaymentTypeDesc
)
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[sp_PaymentType_Update]') and OBJECTPROPERTY(id, N'IsProcedure') = 1) drop procedure [dbo].[sp_PaymentType_Update]
GO

CREATE PROCEDURE [dbo].[sp_PaymentType_Update]
	@sPaymentTypeID nvarchar(10),
	@sPaymentType nvarchar(30),
	@sPaymentTypeDesc nvarchar(30)
AS
SET NOCOUNT ON
UPDATE [dbo].[PaymentType]
SET 
	[PaymentType] = @sPaymentType,
	[PaymentTypeDesc] = @sPaymentTypeDesc
WHERE
	[PaymentTypeID] = @sPaymentTypeID
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[sp_PaymentType_Delete]') and OBJECTPROPERTY(id, N'IsProcedure') = 1) drop procedure [dbo].[sp_PaymentType_Delete]
GO

CREATE PROCEDURE [dbo].[sp_PaymentType_Delete]
	@sPaymentTypeID nvarchar(10)
AS
SET NOCOUNT ON
DELETE FROM [dbo].[PaymentType]
WHERE
	[PaymentTypeID] = @sPaymentTypeID
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[sp_PaymentType_SelectOne]') and OBJECTPROPERTY(id, N'IsProcedure') = 1) drop procedure [dbo].[sp_PaymentType_SelectOne]
GO

CREATE PROCEDURE [dbo].[sp_PaymentType_SelectOne]
	@sPaymentTypeID nvarchar(10)
AS
SET NOCOUNT ON
SELECT
	[PaymentTypeID],
	[PaymentType],
	[PaymentTypeDesc]
FROM [dbo].[PaymentType]
WHERE
	[PaymentTypeID] = @sPaymentTypeID
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[sp_PaymentType_SelectAll]') and OBJECTPROPERTY(id, N'IsProcedure') = 1) drop procedure [dbo].[sp_PaymentType_SelectAll]
GO

CREATE PROCEDURE [dbo].[sp_PaymentType_SelectAll]
AS
SET NOCOUNT ON
SELECT
	[PaymentTypeID],
	[PaymentType],
	[PaymentTypeDesc]
FROM [dbo].[PaymentType]
ORDER BY 
	[PaymentTypeID] ASC
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[sp_Report_Insert]') and OBJECTPROPERTY(id, N'IsProcedure') = 1) drop procedure [dbo].[sp_Report_Insert]
GO

CREATE PROCEDURE [dbo].[sp_Report_Insert]
	@sReportType nvarchar(30),
	@sReportName nvarchar(50),
	@sReportDesc nvarchar(50),
	@iReportID int OUTPUT
AS
SET NOCOUNT ON
INSERT [dbo].[Report]
(
	[ReportType],
	[ReportName],
	[ReportDesc]
)
VALUES
(
	@sReportType,
	@sReportName,
	@sReportDesc
)
SELECT @iReportID=SCOPE_IDENTITY()
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[sp_Report_Update]') and OBJECTPROPERTY(id, N'IsProcedure') = 1) drop procedure [dbo].[sp_Report_Update]
GO

CREATE PROCEDURE [dbo].[sp_Report_Update]
	@iReportID int,
	@sReportType nvarchar(30),
	@sReportName nvarchar(50),
	@sReportDesc nvarchar(50)
AS
SET NOCOUNT ON
UPDATE [dbo].[Report]
SET 
	[ReportType] = @sReportType,
	[ReportName] = @sReportName,
	[ReportDesc] = @sReportDesc
WHERE
	[ReportID] = @iReportID
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[sp_Report_Delete]') and OBJECTPROPERTY(id, N'IsProcedure') = 1) drop procedure [dbo].[sp_Report_Delete]
GO

CREATE PROCEDURE [dbo].[sp_Report_Delete]
	@iReportID int
AS
SET NOCOUNT ON
DELETE FROM [dbo].[Report]
WHERE
	[ReportID] = @iReportID
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[sp_Report_SelectOne]') and OBJECTPROPERTY(id, N'IsProcedure') = 1) drop procedure [dbo].[sp_Report_SelectOne]
GO

CREATE PROCEDURE [dbo].[sp_Report_SelectOne]
	@iReportID int
AS
SET NOCOUNT ON
SELECT
	[ReportID],
	[ReportType],
	[ReportName],
	[ReportDesc]
FROM [dbo].[Report]
WHERE
	[ReportID] = @iReportID
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[sp_Report_SelectAll]') and OBJECTPROPERTY(id, N'IsProcedure') = 1) drop procedure [dbo].[sp_Report_SelectAll]
GO

CREATE PROCEDURE [dbo].[sp_Report_SelectAll]
AS
SET NOCOUNT ON
SELECT
	[ReportID],
	[ReportType],
	[ReportName],
	[ReportDesc]
FROM [dbo].[Report]
ORDER BY 
	[ReportID] ASC
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[sp_Reservation_Insert]') and OBJECTPROPERTY(id, N'IsProcedure') = 1) drop procedure [dbo].[sp_Reservation_Insert]
GO

CREATE PROCEDURE [dbo].[sp_Reservation_Insert]
	@iCustomerID int,
	@iExpectedCheckinDate int,
	@iExpectedCheckoutDate int,
	@daArrivalTime datetime,
	@iNumberGuests int,
	@iRoomTypeID int,
	@sRoomRate nvarchar(10),
	@sComents text,
	@iReservationID int OUTPUT
AS
SET NOCOUNT ON
INSERT [dbo].[Reservation]
(
	[CustomerID],
	[ExpectedCheckinDate],
	[ExpectedCheckoutDate],
	[ArrivalTime],
	[NumberGuests],
	[RoomTypeID],
	[RoomRate],
	[Coments]
)
VALUES
(
	@iCustomerID,
	@iExpectedCheckinDate,
	@iExpectedCheckoutDate,
	@daArrivalTime,
	@iNumberGuests,
	@iRoomTypeID,
	@sRoomRate,
	@sComents
)
SELECT @iReservationID=SCOPE_IDENTITY()
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[sp_Reservation_Update]') and OBJECTPROPERTY(id, N'IsProcedure') = 1) drop procedure [dbo].[sp_Reservation_Update]
GO

CREATE PROCEDURE [dbo].[sp_Reservation_Update]
	@iReservationID int,
	@iCustomerID int,
	@iExpectedCheckinDate int,
	@iExpectedCheckoutDate int,
	@daArrivalTime datetime,
	@iNumberGuests int,
	@iRoomTypeID int,
	@sRoomRate nvarchar(10),
	@sComents text
AS
SET NOCOUNT ON
UPDATE [dbo].[Reservation]
SET 
	[CustomerID] = @iCustomerID,
	[ExpectedCheckinDate] = @iExpectedCheckinDate,
	[ExpectedCheckoutDate] = @iExpectedCheckoutDate,
	[ArrivalTime] = @daArrivalTime,
	[NumberGuests] = @iNumberGuests,
	[RoomTypeID] = @iRoomTypeID,
	[RoomRate] = @sRoomRate,
	[Coments] = @sComents
WHERE
	[ReservationID] = @iReservationID
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[sp_Reservation_UpdateAllWCustomerIDLogic]') and OBJECTPROPERTY(id, N'IsProcedure') = 1) drop procedure [dbo].[sp_Reservation_UpdateAllWCustomerIDLogic]
GO

CREATE PROCEDURE [dbo].[sp_Reservation_UpdateAllWCustomerIDLogic]
	@iCustomerID int,
	@iCustomerIDOld int,
AS
SET NOCOUNT ON
UPDATE [dbo].[Reservation]
SET
	[CustomerID] = @iCustomerID
WHERE
	[CustomerID] = @iCustomerIDOld
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[sp_Reservation_UpdateAllWRoomTypeIDLogic]') and OBJECTPROPERTY(id, N'IsProcedure') = 1) drop procedure [dbo].[sp_Reservation_UpdateAllWRoomTypeIDLogic]
GO

CREATE PROCEDURE [dbo].[sp_Reservation_UpdateAllWRoomTypeIDLogic]
	@iRoomTypeID int,
	@iRoomTypeIDOld int,
AS
SET NOCOUNT ON
UPDATE [dbo].[Reservation]
SET
	[RoomTypeID] = @iRoomTypeID
WHERE
	[RoomTypeID] = @iRoomTypeIDOld
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[sp_Reservation_Delete]') and OBJECTPROPERTY(id, N'IsProcedure') = 1) drop procedure [dbo].[sp_Reservation_Delete]
GO

CREATE PROCEDURE [dbo].[sp_Reservation_Delete]
	@iReservationID int
AS
SET NOCOUNT ON
DELETE FROM [dbo].[Reservation]
WHERE
	[ReservationID] = @iReservationID
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[sp_Reservation_SelectOne]') and OBJECTPROPERTY(id, N'IsProcedure') = 1) drop procedure [dbo].[sp_Reservation_SelectOne]
GO

CREATE PROCEDURE [dbo].[sp_Reservation_SelectOne]
	@iReservationID int
AS
SET NOCOUNT ON
SELECT
	[ReservationID],
	[CustomerID],
	[ExpectedCheckinDate],
	[ExpectedCheckoutDate],
	[ArrivalTime],
	[NumberGuests],
	[RoomTypeID],
	[RoomRate],
	[Coments]
FROM [dbo].[Reservation]
WHERE
	[ReservationID] = @iReservationID
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[sp_Reservation_SelectAll]') and OBJECTPROPERTY(id, N'IsProcedure') = 1) drop procedure [dbo].[sp_Reservation_SelectAll]
GO

CREATE PROCEDURE [dbo].[sp_Reservation_SelectAll]
AS
SET NOCOUNT ON
SELECT
	[ReservationID],
	[CustomerID],
	[ExpectedCheckinDate],
	[ExpectedCheckoutDate],
	[ArrivalTime],
	[NumberGuests],
	[RoomTypeID],
	[RoomRate],
	[Coments]
FROM [dbo].[Reservation]
ORDER BY 
	[ReservationID] ASC
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[sp_Reservation_SelectAllWCustomerIDLogic]') and OBJECTPROPERTY(id, N'IsProcedure') = 1) drop procedure [dbo].[sp_Reservation_SelectAllWCustomerIDLogic]
GO

CREATE PROCEDURE [dbo].[sp_Reservation_SelectAllWCustomerIDLogic]
	@iCustomerID int
AS
SET NOCOUNT ON
SELECT
	[ReservationID],
	[CustomerID],
	[ExpectedCheckinDate],
	[ExpectedCheckoutDate],
	[ArrivalTime],
	[NumberGuests],
	[RoomTypeID],
	[RoomRate],
	[Coments]
FROM [dbo].[Reservation]
WHERE
	[CustomerID] = @iCustomerID
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[sp_Reservation_SelectAllWRoomTypeIDLogic]') and OBJECTPROPERTY(id, N'IsProcedure') = 1) drop procedure [dbo].[sp_Reservation_SelectAllWRoomTypeIDLogic]
GO

CREATE PROCEDURE [dbo].[sp_Reservation_SelectAllWRoomTypeIDLogic]
	@iRoomTypeID int
AS
SET NOCOUNT ON
SELECT
	[ReservationID],
	[CustomerID],
	[ExpectedCheckinDate],
	[ExpectedCheckoutDate],
	[ArrivalTime],
	[NumberGuests],
	[RoomTypeID],
	[RoomRate],
	[Coments]
FROM [dbo].[Reservation]
WHERE
	[RoomTypeID] = @iRoomTypeID
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[sp_Role_Insert]') and OBJECTPROPERTY(id, N'IsProcedure') = 1) drop procedure [dbo].[sp_Role_Insert]
GO

CREATE PROCEDURE [dbo].[sp_Role_Insert]
	@sRoleName nvarchar(50),
	@sRoleDescription nvarchar(50),
	@iRoleID int OUTPUT
AS
SET NOCOUNT ON
INSERT [dbo].[Role]
(
	[RoleName],
	[RoleDescription]
)
VALUES
(
	@sRoleName,
	@sRoleDescription
)
SELECT @iRoleID=SCOPE_IDENTITY()
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[sp_Role_Update]') and OBJECTPROPERTY(id, N'IsProcedure') = 1) drop procedure [dbo].[sp_Role_Update]
GO

CREATE PROCEDURE [dbo].[sp_Role_Update]
	@iRoleID int,
	@sRoleName nvarchar(50),
	@sRoleDescription nvarchar(50)
AS
SET NOCOUNT ON
UPDATE [dbo].[Role]
SET 
	[RoleName] = @sRoleName,
	[RoleDescription] = @sRoleDescription
WHERE
	[RoleID] = @iRoleID
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[sp_Role_Delete]') and OBJECTPROPERTY(id, N'IsProcedure') = 1) drop procedure [dbo].[sp_Role_Delete]
GO

CREATE PROCEDURE [dbo].[sp_Role_Delete]
	@iRoleID int
AS
SET NOCOUNT ON
DELETE FROM [dbo].[Role]
WHERE
	[RoleID] = @iRoleID
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[sp_Role_SelectOne]') and OBJECTPROPERTY(id, N'IsProcedure') = 1) drop procedure [dbo].[sp_Role_SelectOne]
GO

CREATE PROCEDURE [dbo].[sp_Role_SelectOne]
	@iRoleID int
AS
SET NOCOUNT ON
SELECT
	[RoleID],
	[RoleName],
	[RoleDescription]
FROM [dbo].[Role]
WHERE
	[RoleID] = @iRoleID
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[sp_Role_SelectAll]') and OBJECTPROPERTY(id, N'IsProcedure') = 1) drop procedure [dbo].[sp_Role_SelectAll]
GO

CREATE PROCEDURE [dbo].[sp_Role_SelectAll]
AS
SET NOCOUNT ON
SELECT
	[RoleID],
	[RoleName],
	[RoleDescription]
FROM [dbo].[Role]
ORDER BY 
	[RoleID] ASC
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[sp_Room_Insert]') and OBJECTPROPERTY(id, N'IsProcedure') = 1) drop procedure [dbo].[sp_Room_Insert]
GO

CREATE PROCEDURE [dbo].[sp_Room_Insert]
	@iRoomNumber int,
	@iRoomTypeID int,
	@iRoomStatusID int,
	@sDescription nvarchar(50),
	@iRoomID int OUTPUT
AS
SET NOCOUNT ON
INSERT [dbo].[Room]
(
	[RoomNumber],
	[RoomTypeID],
	[RoomStatusID],
	[Description]
)
VALUES
(
	@iRoomNumber,
	@iRoomTypeID,
	@iRoomStatusID,
	@sDescription
)
SELECT @iRoomID=SCOPE_IDENTITY()
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[sp_Room_Update]') and OBJECTPROPERTY(id, N'IsProcedure') = 1) drop procedure [dbo].[sp_Room_Update]
GO

CREATE PROCEDURE [dbo].[sp_Room_Update]
	@iRoomID int,
	@iRoomNumber int,
	@iRoomTypeID int,
	@iRoomStatusID int,
	@sDescription nvarchar(50)
AS
SET NOCOUNT ON
UPDATE [dbo].[Room]
SET 
	[RoomNumber] = @iRoomNumber,
	[RoomTypeID] = @iRoomTypeID,
	[RoomStatusID] = @iRoomStatusID,
	[Description] = @sDescription
WHERE
	[RoomID] = @iRoomID
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[sp_Room_UpdateAllWRoomTypeIDLogic]') and OBJECTPROPERTY(id, N'IsProcedure') = 1) drop procedure [dbo].[sp_Room_UpdateAllWRoomTypeIDLogic]
GO

CREATE PROCEDURE [dbo].[sp_Room_UpdateAllWRoomTypeIDLogic]
	@iRoomTypeID int,
	@iRoomTypeIDOld int,
AS
SET NOCOUNT ON
UPDATE [dbo].[Room]
SET
	[RoomTypeID] = @iRoomTypeID
WHERE
	[RoomTypeID] = @iRoomTypeIDOld
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[sp_Room_UpdateAllWRoomStatusIDLogic]') and OBJECTPROPERTY(id, N'IsProcedure') = 1) drop procedure [dbo].[sp_Room_UpdateAllWRoomStatusIDLogic]
GO

CREATE PROCEDURE [dbo].[sp_Room_UpdateAllWRoomStatusIDLogic]
	@iRoomStatusID int,
	@iRoomStatusIDOld int,
AS
SET NOCOUNT ON
UPDATE [dbo].[Room]
SET
	[RoomStatusID] = @iRoomStatusID
WHERE
	[RoomStatusID] = @iRoomStatusIDOld
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[sp_Room_Delete]') and OBJECTPROPERTY(id, N'IsProcedure') = 1) drop procedure [dbo].[sp_Room_Delete]
GO

CREATE PROCEDURE [dbo].[sp_Room_Delete]
	@iRoomID int
AS
SET NOCOUNT ON
DELETE FROM [dbo].[Room]
WHERE
	[RoomID] = @iRoomID
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[sp_Room_SelectOne]') and OBJECTPROPERTY(id, N'IsProcedure') = 1) drop procedure [dbo].[sp_Room_SelectOne]
GO

CREATE PROCEDURE [dbo].[sp_Room_SelectOne]
	@iRoomID int
AS
SET NOCOUNT ON
SELECT
	[RoomID],
	[RoomNumber],
	[RoomTypeID],
	[RoomStatusID],
	[Description]
FROM [dbo].[Room]
WHERE
	[RoomID] = @iRoomID
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[sp_Room_SelectAll]') and OBJECTPROPERTY(id, N'IsProcedure') = 1) drop procedure [dbo].[sp_Room_SelectAll]
GO

CREATE PROCEDURE [dbo].[sp_Room_SelectAll]
AS
SET NOCOUNT ON
SELECT
	[RoomID],
	[RoomNumber],
	[RoomTypeID],
	[RoomStatusID],
	[Description]
FROM [dbo].[Room]
ORDER BY 
	[RoomID] ASC
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[sp_Room_SelectAllWRoomTypeIDLogic]') and OBJECTPROPERTY(id, N'IsProcedure') = 1) drop procedure [dbo].[sp_Room_SelectAllWRoomTypeIDLogic]
GO

CREATE PROCEDURE [dbo].[sp_Room_SelectAllWRoomTypeIDLogic]
	@iRoomTypeID int
AS
SET NOCOUNT ON
SELECT
	[RoomID],
	[RoomNumber],
	[RoomTypeID],
	[RoomStatusID],
	[Description]
FROM [dbo].[Room]
WHERE
	[RoomTypeID] = @iRoomTypeID
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[sp_Room_SelectAllWRoomStatusIDLogic]') and OBJECTPROPERTY(id, N'IsProcedure') = 1) drop procedure [dbo].[sp_Room_SelectAllWRoomStatusIDLogic]
GO

CREATE PROCEDURE [dbo].[sp_Room_SelectAllWRoomStatusIDLogic]
	@iRoomStatusID int
AS
SET NOCOUNT ON
SELECT
	[RoomID],
	[RoomNumber],
	[RoomTypeID],
	[RoomStatusID],
	[Description]
FROM [dbo].[Room]
WHERE
	[RoomStatusID] = @iRoomStatusID
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[sp_RoomStatus_Insert]') and OBJECTPROPERTY(id, N'IsProcedure') = 1) drop procedure [dbo].[sp_RoomStatus_Insert]
GO

CREATE PROCEDURE [dbo].[sp_RoomStatus_Insert]
	@sRoomStatusName nvarchar(20),
	@sRoomStatusDesc nvarchar(50),
	@iRoomStatusID int OUTPUT
AS
SET NOCOUNT ON
INSERT [dbo].[RoomStatus]
(
	[RoomStatusName],
	[RoomStatusDesc]
)
VALUES
(
	@sRoomStatusName,
	@sRoomStatusDesc
)
SELECT @iRoomStatusID=SCOPE_IDENTITY()
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[sp_RoomStatus_Update]') and OBJECTPROPERTY(id, N'IsProcedure') = 1) drop procedure [dbo].[sp_RoomStatus_Update]
GO

CREATE PROCEDURE [dbo].[sp_RoomStatus_Update]
	@iRoomStatusID int,
	@sRoomStatusName nvarchar(20),
	@sRoomStatusDesc nvarchar(50)
AS
SET NOCOUNT ON
UPDATE [dbo].[RoomStatus]
SET 
	[RoomStatusName] = @sRoomStatusName,
	[RoomStatusDesc] = @sRoomStatusDesc
WHERE
	[RoomStatusID] = @iRoomStatusID
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[sp_RoomStatus_Delete]') and OBJECTPROPERTY(id, N'IsProcedure') = 1) drop procedure [dbo].[sp_RoomStatus_Delete]
GO

CREATE PROCEDURE [dbo].[sp_RoomStatus_Delete]
	@iRoomStatusID int
AS
SET NOCOUNT ON
DELETE FROM [dbo].[RoomStatus]
WHERE
	[RoomStatusID] = @iRoomStatusID
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[sp_RoomStatus_SelectOne]') and OBJECTPROPERTY(id, N'IsProcedure') = 1) drop procedure [dbo].[sp_RoomStatus_SelectOne]
GO

CREATE PROCEDURE [dbo].[sp_RoomStatus_SelectOne]
	@iRoomStatusID int
AS
SET NOCOUNT ON
SELECT
	[RoomStatusID],
	[RoomStatusName],
	[RoomStatusDesc]
FROM [dbo].[RoomStatus]
WHERE
	[RoomStatusID] = @iRoomStatusID
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[sp_RoomStatus_SelectAll]') and OBJECTPROPERTY(id, N'IsProcedure') = 1) drop procedure [dbo].[sp_RoomStatus_SelectAll]
GO

CREATE PROCEDURE [dbo].[sp_RoomStatus_SelectAll]
AS
SET NOCOUNT ON
SELECT
	[RoomStatusID],
	[RoomStatusName],
	[RoomStatusDesc]
FROM [dbo].[RoomStatus]
ORDER BY 
	[RoomStatusID] ASC
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[sp_RoomType_Insert]') and OBJECTPROPERTY(id, N'IsProcedure') = 1) drop procedure [dbo].[sp_RoomType_Insert]
GO

CREATE PROCEDURE [dbo].[sp_RoomType_Insert]
	@sRoomType nvarchar(50),
	@sRoomDesc nvarchar(50),
	@sRoomRate nvarchar(10),
	@iRoomBeds int,
	@iRoomTypeID int OUTPUT
AS
SET NOCOUNT ON
INSERT [dbo].[RoomType]
(
	[RoomType],
	[RoomDesc],
	[RoomRate],
	[RoomBeds]
)
VALUES
(
	@sRoomType,
	@sRoomDesc,
	@sRoomRate,
	@iRoomBeds
)
SELECT @iRoomTypeID=SCOPE_IDENTITY()
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[sp_RoomType_Update]') and OBJECTPROPERTY(id, N'IsProcedure') = 1) drop procedure [dbo].[sp_RoomType_Update]
GO

CREATE PROCEDURE [dbo].[sp_RoomType_Update]
	@iRoomTypeID int,
	@sRoomType nvarchar(50),
	@sRoomDesc nvarchar(50),
	@sRoomRate nvarchar(10),
	@iRoomBeds int
AS
SET NOCOUNT ON
UPDATE [dbo].[RoomType]
SET 
	[RoomType] = @sRoomType,
	[RoomDesc] = @sRoomDesc,
	[RoomRate] = @sRoomRate,
	[RoomBeds] = @iRoomBeds
WHERE
	[RoomTypeID] = @iRoomTypeID
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[sp_RoomType_Delete]') and OBJECTPROPERTY(id, N'IsProcedure') = 1) drop procedure [dbo].[sp_RoomType_Delete]
GO

CREATE PROCEDURE [dbo].[sp_RoomType_Delete]
	@iRoomTypeID int
AS
SET NOCOUNT ON
DELETE FROM [dbo].[RoomType]
WHERE
	[RoomTypeID] = @iRoomTypeID
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[sp_RoomType_SelectOne]') and OBJECTPROPERTY(id, N'IsProcedure') = 1) drop procedure [dbo].[sp_RoomType_SelectOne]
GO

CREATE PROCEDURE [dbo].[sp_RoomType_SelectOne]
	@iRoomTypeID int
AS
SET NOCOUNT ON
SELECT
	[RoomTypeID],
	[RoomType],
	[RoomDesc],
	[RoomRate],
	[RoomBeds]
FROM [dbo].[RoomType]
WHERE
	[RoomTypeID] = @iRoomTypeID
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[sp_RoomType_SelectAll]') and OBJECTPROPERTY(id, N'IsProcedure') = 1) drop procedure [dbo].[sp_RoomType_SelectAll]
GO

CREATE PROCEDURE [dbo].[sp_RoomType_SelectAll]
AS
SET NOCOUNT ON
SELECT
	[RoomTypeID],
	[RoomType],
	[RoomDesc],
	[RoomRate],
	[RoomBeds]
FROM [dbo].[RoomType]
ORDER BY 
	[RoomTypeID] ASC
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[sp_sysdiagrams_Insert]') and OBJECTPROPERTY(id, N'IsProcedure') = 1) drop procedure [dbo].[sp_sysdiagrams_Insert]
GO

CREATE PROCEDURE [dbo].[sp_sysdiagrams_Insert]
	@sname nvarchar(128),
	@iprincipal_id int,
	@iversion int,
	@bidefinition varbinary(-1),
	@idiagram_id int OUTPUT
AS
SET NOCOUNT ON
INSERT [dbo].[sysdiagrams]
(
	[name],
	[principal_id],
	[version],
	[definition]
)
VALUES
(
	@sname,
	@iprincipal_id,
	@iversion,
	@bidefinition
)
SELECT @idiagram_id=SCOPE_IDENTITY()
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[sp_sysdiagrams_Update]') and OBJECTPROPERTY(id, N'IsProcedure') = 1) drop procedure [dbo].[sp_sysdiagrams_Update]
GO

CREATE PROCEDURE [dbo].[sp_sysdiagrams_Update]
	@sname nvarchar(128),
	@iprincipal_id int,
	@idiagram_id int,
	@iversion int,
	@bidefinition varbinary(-1)
AS
SET NOCOUNT ON
UPDATE [dbo].[sysdiagrams]
SET 
	[name] = @sname,
	[principal_id] = @iprincipal_id,
	[version] = @iversion,
	[definition] = @bidefinition
WHERE
	[diagram_id] = @idiagram_id
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[sp_sysdiagrams_Delete]') and OBJECTPROPERTY(id, N'IsProcedure') = 1) drop procedure [dbo].[sp_sysdiagrams_Delete]
GO

CREATE PROCEDURE [dbo].[sp_sysdiagrams_Delete]
	@idiagram_id int
AS
SET NOCOUNT ON
DELETE FROM [dbo].[sysdiagrams]
WHERE
	[diagram_id] = @idiagram_id
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[sp_sysdiagrams_SelectOne]') and OBJECTPROPERTY(id, N'IsProcedure') = 1) drop procedure [dbo].[sp_sysdiagrams_SelectOne]
GO

CREATE PROCEDURE [dbo].[sp_sysdiagrams_SelectOne]
	@idiagram_id int
AS
SET NOCOUNT ON
SELECT
	[name],
	[principal_id],
	[diagram_id],
	[version],
	[definition]
FROM [dbo].[sysdiagrams]
WHERE
	[diagram_id] = @idiagram_id
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[sp_sysdiagrams_SelectOneWnameLogic]') and OBJECTPROPERTY(id, N'IsProcedure') = 1) drop procedure [dbo].[sp_sysdiagrams_SelectOneWnameLogic]
GO

CREATE PROCEDURE [dbo].[sp_sysdiagrams_SelectOneWnameLogic]
	@sname nvarchar(128)
AS
SET NOCOUNT ON
SELECT
	[name],
	[principal_id],
	[diagram_id],
	[version],
	[definition]
FROM [dbo].[sysdiagrams]
WHERE
	[name] = @sname
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[sp_sysdiagrams_SelectOneWprincipal_idLogic]') and OBJECTPROPERTY(id, N'IsProcedure') = 1) drop procedure [dbo].[sp_sysdiagrams_SelectOneWprincipal_idLogic]
GO

CREATE PROCEDURE [dbo].[sp_sysdiagrams_SelectOneWprincipal_idLogic]
	@iprincipal_id int
AS
SET NOCOUNT ON
SELECT
	[name],
	[principal_id],
	[diagram_id],
	[version],
	[definition]
FROM [dbo].[sysdiagrams]
WHERE
	[principal_id] = @iprincipal_id
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[sp_sysdiagrams_SelectAll]') and OBJECTPROPERTY(id, N'IsProcedure') = 1) drop procedure [dbo].[sp_sysdiagrams_SelectAll]
GO

CREATE PROCEDURE [dbo].[sp_sysdiagrams_SelectAll]
AS
SET NOCOUNT ON
SELECT
	[name],
	[principal_id],
	[diagram_id],
	[version],
	[definition]
FROM [dbo].[sysdiagrams]
ORDER BY 
	[diagram_id] ASC
GO

